﻿Imports System.Data.SqlClient

Public Class cCaseCategory

    Dim con As New SqlConnection(strcon)

    Public Function select_CaseCategory() As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT CaseCategoryID,CaseCategoryName,Description FROM tblCaseCategory WHERE Active=1")
        Return dt
    End Function

    Public Function select_CaseCategory_byID(ByVal CaseCategoryID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT CaseCategoryID,CaseCategoryName,Description FROM tblCaseCategory WHERE Active=1 AND CaseCategoryID=N'" & CaseCategoryID & "'")
        Return dt
    End Function

    Public Function select_CaseCategory_byKeyword(ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT CaseCategoryID,CaseCategoryName,Description FROM tblCaseCategory WHERE Active=1 AND LOWER(CaseCategoryName + Description) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function

    Public Function insert_CaseCategory(ByVal CaseCategoryName As String, ByVal Description As String) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO tblCaseCategory(CaseCategoryName,Description) VALUES(@CaseCategoryName,@Description)"
        With cmd.Parameters
            .AddWithValue("@CaseCategoryName", CaseCategoryName)
            .AddWithValue("@Description", Description)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function update_CaseCategory(ByVal CaseCategoryID As Long, ByVal CaseCategoryName As String, ByVal Description As String) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblCaseCategory SET CaseCategoryName=@CaseCategoryName,Description=@Description WHERE CaseCategoryID=@CaseCategoryID"
        With cmd.Parameters
            .AddWithValue("@CaseCategoryID", CaseCategoryID)
            .AddWithValue("@CaseCategoryName", CaseCategoryName)
            .AddWithValue("@Description", Description)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function delete_CaseCategory(ByVal CaseCategoryID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "DELETE FROM tblCaseCategory WHERE CaseCategoryID=@CaseCategoryID"
        With cmd.Parameters
            .AddWithValue("@CaseCategoryID", CaseCategoryID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

End Class
